<?php defined('SYSPATH') or die('No direct script access.');

class Profiles_Model extends Model {

	protected $temp_filename;
	protected $temp_evident_filename;
	
	public function __construct() {		
		$this->db = Database::instance();
	}
	
	public function count_members()
    { 
    	$sql = '
				SELECT count(*) as total
				FROM users u INNER JOIN profiles p ON u.id=p.id
				WHERE u.username <> \'admin\'
    			';
		$result = $this->db->query($sql)->current()->total;
		return $result;
    }

    public function browse($limit, $offset)
    {
    	$sql = '
				SELECT u.id, 
				p.prefix_id,
				(SELECT title FROM masters WHERE type_id=2 AND id=p.prefix_id) AS prefix, 
				p.firstname, 
				p.lastname, 
				u.email, 
				p.status as status_id,
				(SELECT title FROM masters WHERE type_id=6 AND id=p.status) AS status, 
				p.member_type as member_type_id,
				(SELECT title FROM masters WHERE type_id=5 AND id=p.member_type) AS member_type, 
				p.expire_date
				FROM users u INNER JOIN profiles p ON u.id=p.id
				WHERE u.username <> \'admin\'
				ORDER BY p.firstname, p.lastname
				LIMIT ?, ?;
    	';
		$result = $this->db->query($sql, array($offset, $limit));
		return $result;
    }
    
    public function load($id) {
    	$sql = '
			SELECT u.id, u.username, u.email, p.prefix_id, p.firstname, p.lastname, p.birthdate, p.sex, p.question_id, p.answer, p.status, p.expire_date, p.member_type, p.picture
			FROM users u INNER JOIN profiles p ON u.id=p.id
			WHERE u.id = ?
    	';
		$query = $this->db->query($sql, array($id));
		return $query->result(FALSE);
    }
	
    public function load_clubs($id) {
    	$sql ='
			SELECT cp.club_id, cp.profiles_id, c.name 
			FROM clubs_profiles cp INNER JOIN clubs c ON cp.club_id = c.id
			WHERE profiles_id=?;    	
    	';
    	$query = $this->db->query($sql, array($id));
		return $query->result(FALSE);
    }

    public function add_club($profile_id, $club_id) {
    	$this->db->query('BEGIN');
		try {
			$sql = 'SELECT count(*) as exist_club FROM clubs_profiles WHERE club_id = ? AND profiles_id=? ';
			$query = $this->db->query($sql, array($club_id, $profile_id));
			if ($query->current()->exist_club <= 0) {
				$sql = 'INSERT INTO clubs_profiles 
						(club_id, profiles_id, created)
						VALUES(?,?,CURRENT_TIMESTAMP())';
				$query = $this->db->query($sql, array($club_id, $profile_id));
			}
			$this->db->query('COMMIT');
			return true;
		}	
		catch (Exception $e) {
			$this->db->query('ROLLBACK');
			Kohana::log('error', $e);
			return false;
		}	
    }

    public function del_club($profile_id, $club_id) {
    	$this->db->query('BEGIN');
		try {
			$sql = 'DELETE FROM clubs_profiles WHERE club_id=? AND profiles_id=? ';
			$query = $this->db->query($sql, array($club_id, $profile_id));
			
			$this->db->query('COMMIT');
			return true;
		}	
		catch (Exception $e) {
			$this->db->query('ROLLBACK');
			Kohana::log('error', $e);
			return false;
		}	
    }
        
    public function load_genes($id) {
    	$sql ='
			SELECT gp.gene_id, gp.profile_id, g.name 
			FROM genes_profiles gp INNER JOIN genes g ON gp.gene_id = g.id
			WHERE profile_id=?;    	
    	';
    	$query = $this->db->query($sql, array($id));
		return $query->result(FALSE);
    }    
    
	public function add_gene($profile_id, $gene_id) {
    	$this->db->query('BEGIN');
		try {
			$sql = 'SELECT count(*) as exist_gene FROM genes_profiles WHERE gene_id = ? AND profile_id=? ';
			$query = $this->db->query($sql, array($gene_id, $profile_id));
			if ($query->current()->exist_gene <= 0) {
				$sql = 'INSERT INTO genes_profiles 
						(gene_id, profile_id, created)
						VALUES(?,?,CURRENT_TIMESTAMP())';
				$query = $this->db->query($sql, array($gene_id, $profile_id));
			}
			$this->db->query('COMMIT');
			return true;
		}	
		catch (Exception $e) {
			$this->db->query('ROLLBACK');
			Kohana::log('error', $e);
			return false;
		}	
    }
    
   	public function del_gene($profile_id, $gene_id) {
    	$this->db->query('BEGIN');
		try {
			$sql = 'DELETE FROM genes_profiles WHERE gene_id=? AND profile_id=? ';
			$query = $this->db->query($sql, array($gene_id, $profile_id));
			
			$this->db->query('COMMIT');
			return true;
		}	
		catch (Exception $e) {
			$this->db->query('ROLLBACK');
			Kohana::log('error', $e);
			return false;
		}	
    }
    
	public function register($data) {
		$this->db->query('BEGIN');
		try {
			$sql = 'SELECT count(*) as exist_user FROM users WHERE username = ?';
			$query = $this->db->query($sql, array($data['username']));
			if ($query->current()->exist_user <= 0) {
				$sql = 'INSERT INTO users 
						(email, username, password, logins, last_login)
						VALUES(?,?,?,?,?)';
				$query = $this->db->query($sql, array($data['email'], $data['username'], Auth::instance()->hash_password($data['password'],FALSE), 0, NULL));

				$sql = 'SELECT id 
						FROM users 
						WHERE (username=?)';
				$id = $this->db->query($sql, array($data['username']))->current()->id;

				$sql = 'INSERT INTO profiles 
						(id, prefix_id, firstname, lastname, birthdate, sex, question_id, answer, status, expire_date, member_type, picture, created, updated)
						VALUES(?,?,?,?,?,?,?,?,?,CURRENT_TIMESTAMP(),?,?,CURRENT_TIMESTAMP(),?)';
				
				if (empty($data['picture'])) {
					$query = $this->db->query($sql, array($id, $data['prefix_id'], $data['firstname'], $data['lastname'], $data['birthdate'], $data['sex'], $data['question_id'], $data['answer'], 2, $data['member_type'], NULL, NULL));
				}
				else {
					$query = $this->db->query($sql, array($id, $data['prefix_id'], $data['firstname'], $data['lastname'], $data['birthdate'], $data['sex'], $data['question_id'], $data['answer'], 2, $data['member_type'], $data['picture'], NULL));
				}
			}
			$this->db->query('COMMIT');
			return true;
		}	
		catch (Exception $e) {
			$this->db->query('ROLLBACK');
			Kohana::log('error', $e);
			return false;
		}	
	}
	
	public function paid($id) {
		$this->db->query('BEGIN');
		try {
			$sql = 'SELECT member_type 
					FROM profiles 
					WHERE (id=?)';
			$type = $this->db->query($sql, array($id))->current()->member_type;
			
			$sql = '
					UPDATE profiles 
						SET status=1, 
						expire_date=CURDATE() + INTERVAL 1 YEAR, 
						updated=CURRENT_TIMESTAMP() 
					WHERE id=?
					';
			$query = $this->db->query($sql, array($id));
			
			$sql = 'SELECT count(*) as exist_role
					FROM roles_users
					WHERE user_id=? AND role_id=?
					';
			if ($this->db->query($sql, array($id, 1))->current()->exist_role == 0) {
				$sql = 'INSERT INTO roles_users 
					(user_id, role_id) 
					VALUES(?, ?)';
				$query = $this->db->query($sql, array($id, 1));	// Login
			}
			
			$sql = 'SELECT count(*) as exist_role
					FROM roles_users
					WHERE user_id=? AND role_id=?
					';
			if ($this->db->query($sql, array($id, $type+1))->current()->exist_role == 0) {
				$sql = 'INSERT INTO roles_users 
					(user_id, role_id) 
					VALUES(?, ?)';
				$query = $this->db->query($sql, array($id, $type+1));	// Member Type
			}
			
			$this->db->query('COMMIT');
			return true;
		}	
		catch (Exception $e) {
			$this->db->query('ROLLBACK');
			Kohana::log('error', $e);
			return false;
		}	
	}
	
	public function update($data) {
		$this->db->query('BEGIN');
		try {
			$sql = '
					UPDATE users 
					SET email=?
					WHERE id=?
					';
			$query = $this->db->query($sql, array($data['email'], $data['id']));

			if (empty($data['picture'])) {
				$sql = '
						UPDATE profiles 
						SET prefix_id=?, 
							firstname=?, 
							lastname=?, 
							birthdate=?, 
							sex=?, 
							question_id=?, 
							answer=?, 
							updated=CURRENT_TIMESTAMP()
						WHERE id=?
						';
				$query = $this->db->query($sql, array($data['prefix_id'], $data['firstname'], $data['lastname'], $data['birthdate'], $data['sex'], $data['question_id'], $data['answer'], $data['id']));
			} 
			else {
				$sql = '
						UPDATE profiles 
						SET prefix_id=?, 
							firstname=?, 
							lastname=?, 
							birthdate=?, 
							sex=?, 
							question_id=?, 
							answer=?,
							picture=?, 
							updated=CURRENT_TIMESTAMP()
						WHERE id=?
						';
				$query = $this->db->query($sql, array($data['prefix_id'], $data['firstname'], $data['lastname'], $data['birthdate'], $data['sex'], $data['question_id'], $data['answer'], $data['picture'], $data['id']));
			}
			
			$this->db->query('COMMIT');
			return true;
		}	
		catch (Exception $e) {
			$this->db->query('ROLLBACK');
			Kohana::log('error', $e);
			return false;
		}					
	}
	
	public function change_password($data) {
		$this->db->query('BEGIN');
		try {
			$sql = '
					UPDATE users 
					SET password=?
					WHERE id=?
					';
			$hash_pwd = Auth::instance()->hash_password($data['password'],FALSE);
			$query = $this->db->query($sql, array($hash_pwd, $data['id']));
						
			$this->db->query('COMMIT');
			return true;
		}	
		catch (Exception $e) {
			$this->db->query('ROLLBACK');
			Kohana::log('error', $e);
			return false;
		}					
	}
	
	public function delete($id) {
		$this->db->query('BEGIN');
		try {
			$config = Kohana::config('hsm');
			$filename = $this->get_image_fileneme($id);
			if (!empty($filename)) {
				$filename = $config['upload_profiles'].basename($filename);
			}
			
			$sql = 'DELETE FROM clubs_profiles WHERE profiles_id=?;';
			$query = $this->db->query($sql, array($id));

			$sql = 'DELETE FROM genes_profiles WHERE profile_id=?;';
			$query = $this->db->query($sql, array($id));

			$sql = 'DELETE FROM profiles_download_songs WHERE profile_id=?;';
			$query = $this->db->query($sql, array($id));
			
			$sql = 'DELETE FROM profiles_vote_channels WHERE profile_id=?;';
			$query = $this->db->query($sql, array($id));

			$sql = 'DELETE FROM profiles_download_songs WHERE profile_id=?;';
			$query = $this->db->query($sql, array($id));
			
			$sql = 'DELETE FROM channels_playlists WHERE owner_id=?;';
			$query = $this->db->query($sql, array($id));
			
			$sql = 'DELETE FROM channels WHERE owner_id=?;';
			$query = $this->db->query($sql, array($id));
			
			$sql = 'DELETE FROM profiles WHERE id=?;';
			$query = $this->db->query($sql, array($id));

			$sql = 'DELETE FROM roles_users WHERE user_id=?;';
			$query = $this->db->query($sql, array($id));

			$sql = 'DELETE FROM user_tokens WHERE user_id=?;';
			$query = $this->db->query($sql, array($id));
			
			$sql = 'DELETE FROM users WHERE id=?;';
			$query = $this->db->query($sql, array($id));
			
			$this->db->query('COMMIT');
			if (file_exists($filename)) {
				try {
					unlink($filename);
				}
				catch (Exception $ex){
				}
			}
			return true;
		}	
		catch (Exception $e) {
			$this->db->query('ROLLBACK');
			Kohana::log('error', $e);
			return false;
		}
	}
	
	public function get_image_fileneme($id) {
    	$sql = '
			SELECT picture 
			FROM profiles 
			WHERE id=?;
    	';
		$query = $this->db->query($sql, array($id));
		return $query->current()->picture;
    }
    
	public function get_evident_image_fileneme($id) {
		try {
	    	$sql = '
				SELECT evident_picture 
				FROM profiles 
				WHERE id=?;
	    	';
			$query = $this->db->query($sql, array($id));
			return $query->current()->evident_picture;
		}
		catch (Exception $e) {
			return "";
		} 
    }
    
    public function file_validate($file_name) {
	    $_FILES = Validation::factory($_FILES)
					->add_rules('picture', 
								'upload::valid', 
								'upload::type[gif,jpg,png]', 
								'upload::size[1M]');
		 
		if ($_FILES->validate())
		{
			$config = Kohana::config('hsm');
			
			// Temporary file name
			$this->temp_filename = upload::save($file_name);
			if (file_exists($this->temp_filename)) {
				// Resize, sharpen, and save the image
				Image::factory($this->temp_filename)
					->resize($config['image_profile_max_width'], $config['image_profile_max_height'], Image::AUTO)
					->save($config['upload_profiles'].basename($this->temp_filename));
			 
				// Remove the temporary file
				unlink($this->temp_filename);
				$this->temp_filename = $config['upload_profiles'].basename($this->temp_filename);
			}
			else {
				$this->temp_filename = '';
			}
		}
		return $_FILES;
    }
    
	public function file_evident_validate($file_name) {
	    $_FILES = Validation::factory($_FILES)
					->add_rules('picture', 
								'upload::valid', 
								'upload::type[gif,jpg,png]', 
								'upload::size[1M]');
		 
		if ($_FILES->validate())
		{
			$config = Kohana::config('hsm');
			
			// Temporary file name
			$this->temp_evident_filename = upload::save($file_name);
			if (file_exists($this->temp_evident_filename)) {
				// Resize, sharpen, and save the image
				Image::factory($this->temp_evident_filename)
					->resize($config['image_max_width'], $config['image_max_height'], Image::AUTO)
					->save($config['upload_evident'].basename($this->temp_evident_filename));
			 
				// Remove the temporary file
				unlink($this->temp_evident_filename);
				$this->temp_evident_filename = $config['upload_evident'].basename($this->temp_evident_filename);
			}
			else {
				$this->temp_evident_filename = '';
			}
		}
		return $_FILES;
    }
    
    public function get_filename() {
    	return $this->temp_filename;
    }

    public function get_evident_filename() {
    	return $this->temp_evident_filename;
    }
    
    public function get_new_password($user_id) {
    	try {
    		$new_password = uniqid();
    		$new_password = substr($new_password, 4, 8);
    		$data = array(
    			'id' =>  $user_id,
    			'password' => $new_password,
    		);
    		if ($this->change_password($data)) {
    			$config = Kohana::config('hsm');
    			$to = $this->get_user_email($user_id);
				$from = $config['webmaster_email'];
				$subject = $config['subject_forget_password'];
				$message = $config['content_forget_password'] . $new_password;				
				email::send($to, $from, $subject, $message, TRUE);
				return true;
    		}
    		else {
    			return false;
    		}
    	}
    	catch (Exception $e) {
    		Kohana::log('error', $e);
			return false;
    	}
    }
    
	public function is_valid_user($user_id, $question_id, $answer) {
		try {
			$sql = '
					SELECT count(*) as is_valid
					FROM users u INNER JOIN profiles p ON u.id=p.id
					WHERE (u.id=?) 
					AND (p.question_id=?)
					AND (p.answer=?)
				   ';
			$query = $this->db->query($sql, array($user_id, $question_id, $answer))->current()->is_valid;
			if ($query > 0) {
				return true;
			}
			else  {
				return false;
			}
		}	
		catch (Exception $e) {
			Kohana::log('error', $e);
			return false;
		}					
	}
	
	public function get_user_email($user_id) {
		try {
			$sql ='
				SELECT email as user_email
				FROM users
				WHERE id=?   	
	    	';
			$email = $this->db->query($sql, array($user_id))->current()->user_email;
	    	return $email;
		}
		catch (Exception $e) {
			Kohana::log('error', $e);
			return "";
		}
	}
	
	public function is_expired_user($user_id) {
		try {
			$sql = '
					SELECT Count(*) as expired
					FROM profiles
					WHERE id=?
					AND expire_date>CURDATE()
				   ';
			$query = $this->db->query($sql, array($user_id))->current()->expired;
			if ($query == 0) {
				return true;
			}
			else  {
				return false;
			}
		}	
		catch (Exception $e) {
			Kohana::log('error', $e);
			return false;
		}					
	}
	
	public function user_expired($user_id) {
		$this->db->query('BEGIN');
		try {
			$sql = '
					UPDATE profiles 
						SET status=2, 
						updated=CURRENT_TIMESTAMP() 
					WHERE id=?
					';
			$query = $this->db->query($sql, array($user_id));
						
			$sql = 'DELETE FROM roles_users WHERE user_id=?';
			$query = $this->db->query($sql, array($user_id));
			
			$this->db->query('COMMIT');
			return true;
		}	
		catch (Exception $e) {
			$this->db->query('ROLLBACK');
			Kohana::log('error', $e);
			return false;
		}			
	}

	public function confirm_paid($data) {
		$this->db->query('BEGIN');
		try {
			$sql = 'UPDATE profiles SET reference_id=?, evident_picture=?, updated=CURRENT_TIMESTAMP() WHERE id=?;';
			$query = $this->db->query($sql, array($data['reference_id'], $data['evident_picture'], $data['id']));

			$this->db->query('COMMIT');
			return true;
		}	
		catch (Exception $e) {
			$this->db->query('ROLLBACK');
			Kohana::log('error', $e);
			return false;
		}	
    }
    
    public function get_evident($id) {
    	$sql = '
			SELECT id, firstname, lastname, (SELECT title FROM masters WHERE type_id=5 AND id=p.member_type) AS member_type, reference_id, evident_picture
			FROM profiles p
			WHERE p.id = ?
    	';
		$query = $this->db->query($sql, array($id));
		return $query->result(FALSE);
    }
	
}